{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Querying Microsoft Defender Data\n",
    "MSTICPy versions >= 1.5.0\n",
    "\n",
    "### Description\n",
    "This Notebook provides details and examples of how to connect to and query data from the Microsoft Defender Advanced Hunting API.\n",
    "\n",
    "<p style=\"border: solid; padding: 5pt\"><b>Note: </b>\n",
    "This notebook reflects a partially-updated component and still\n",
    "uses the \"MDATP\" abbreviation to refer to the Microsoft 365 Defender\n",
    "and Microsoft Defender for Endpoint data services.\n",
    "</p>\n",
    "\n",
    "### Installation\n",
    "\n",
    "```python\n",
    "%pip install --upgrade msticpy\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Authentication\n",
    "\n",
    "Authentication for the Microsoft Defender Advanced Hunting API is handled via an Azure AD application. Before you can authenticate you will need to register an application and provide it with the required permissions. MSTICpy supports Application Context authentication to the API.\n",
    "Detailed instructions on registering an application can be found here: \n",
    "- [Get access with an application context](https://docs.microsoft.com/microsoft-365/security/defender-endpoint/exposed-apis-create-app-webapp?view=o365-worldwide)\n",
    "- [Get access with a user context](https://docs.microsoft.com/microsoft-365/security/defender-endpoint/exposed-apis-create-app-nativeapp?view=o365-worldwide)\n",
    "\n",
    "Once created you will require the following details:\n",
    "* Application (client) ID\n",
    "* Directory (tenant) ID\n",
    "* Client secret\n",
    "\n",
    "These details can be found in the Azure Portal under Azure Active Directory > App Registrations.\n",
    "\n",
    "Once collected the easiest way to manage these details is via msticpyconfig.yaml - simply add them to the file in the following format:\n",
    "\n",
    "```yaml\n",
    "DataProviders:\n",
    "  MicrosoftDefender:\n",
    "    Args:\n",
    "      ClientId: \"CLIENT ID\"\n",
    "      ClientSecret:\n",
    "        KeyVault: \n",
    "      TenantId: \"TENANT ID\"\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can then initialize a data provider for Microsoft Defender and connect the provider.\n",
    "\n",
    "Note: you can also provide these values to the connect function.\n",
    "See [Microsoft Defender data provider](https://msticpy.readthedocs.io/en/latest/data_acquisition/DataProviders.html#microsoft-defender)\n",
    "\n",
    "<p style=\"border: solid; padding: 5pt\"><b>Note: </b>\n",
    "If you want to access the Microsoft Defender for Endpoint\n",
    "APIs rather than the M365 Defender API (the latter is a subset\n",
    "of the former), please use \"MDE\" as the parameter to QueryProvider.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connected.\n"
     ]
    }
   ],
   "source": [
    "from msticpy.data.data_providers import QueryProvider\n",
    "md_prov = QueryProvider(\"M365D\")\n",
    "\n",
    "md_prov.connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once connected the Microsoft Defender data connector functions in a similar manner to other data connectors. You can list queries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['MDATP.file_path',\n",
       " 'MDATP.host_alerts',\n",
       " 'MDATP.host_connections',\n",
       " 'MDATP.ip_alerts',\n",
       " 'MDATP.ip_connections',\n",
       " 'MDATP.list_alerts',\n",
       " 'MDATP.list_connections',\n",
       " 'MDATP.list_filehash',\n",
       " 'MDATP.list_files',\n",
       " 'MDATP.list_host_processes',\n",
       " 'MDATP.process_cmd_line',\n",
       " 'MDATP.process_creations',\n",
       " 'MDATP.process_paths',\n",
       " 'MDATP.protocol_connections',\n",
       " 'MDATP.sha1_alerts',\n",
       " 'MDATP.url_alerts',\n",
       " 'MDATP.url_connections',\n",
       " 'MDATP.user_files',\n",
       " 'MDATP.user_logons',\n",
       " 'MDATP.user_network',\n",
       " 'MDATP.user_processes',\n",
       " 'MDATPHunting.accessibility_persistence',\n",
       " 'MDATPHunting.av_sites',\n",
       " 'MDATPHunting.b64_pe',\n",
       " 'MDATPHunting.brute_force',\n",
       " 'MDATPHunting.cve_2018_1000006l',\n",
       " 'MDATPHunting.cve_2018_1111',\n",
       " 'MDATPHunting.cve_2018_4878',\n",
       " 'MDATPHunting.doc_with_link',\n",
       " 'MDATPHunting.dropbox_link',\n",
       " 'MDATPHunting.email_link',\n",
       " 'MDATPHunting.email_smartscreen',\n",
       " 'MDATPHunting.malware_recycle',\n",
       " 'MDATPHunting.network_scans',\n",
       " 'MDATPHunting.powershell_downloads',\n",
       " 'MDATPHunting.service_account_powershell',\n",
       " 'MDATPHunting.smartscreen_ignored',\n",
       " 'MDATPHunting.smb_discovery',\n",
       " 'MDATPHunting.tor',\n",
       " 'MDATPHunting.uncommon_powershell',\n",
       " 'MDATPHunting.user_enumeration']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "md_prov.list_queries()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get details about avaliable queries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query:  list_alerts\n",
      "Data source:  MDE\n",
      "Retrieves list of alerts\n",
      "\n",
      "Parameters\n",
      "----------\n",
      "add_query_items: str (optional)\n",
      "    Additional query clauses\n",
      "end: datetime\n",
      "    Query end time\n",
      "start: datetime\n",
      "    Query start time\n",
      "table: str (optional)\n",
      "    Table name\n",
      "    (default value is: DeviceAlertEvents)\n",
      "Query:\n",
      " {table} | where Timestamp >= datetime({start}) | where Timestamp <= datetime({end}) {add_query_items}\n"
     ]
    }
   ],
   "source": [
    "md_prov.MDATP.list_alerts('?')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Execute queries with default parameters:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AlertId</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>DeviceId</th>\n",
       "      <th>DeviceName</th>\n",
       "      <th>Severity</th>\n",
       "      <th>Category</th>\n",
       "      <th>Title</th>\n",
       "      <th>FileName</th>\n",
       "      <th>SHA1</th>\n",
       "      <th>RemoteUrl</th>\n",
       "      <th>RemoteIP</th>\n",
       "      <th>AttackTechniques</th>\n",
       "      <th>ReportId</th>\n",
       "      <th>Table</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ed637828714417577685_889446803</td>\n",
       "      <td>2022-03-14 16:03:11.918913300+00:00</td>\n",
       "      <td>a085f9e3a00689554d351bdcdd4d503a1983eecd</td>\n",
       "      <td>desktop-6qf1uf2</td>\n",
       "      <td>High</td>\n",
       "      <td>UnwantedSoftware</td>\n",
       "      <td>Notepad Execution Detection</td>\n",
       "      <td>notepad.exe</td>\n",
       "      <td>5b80bbb07b1a84384e61fb3f9366cad97904ebea</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>25654</td>\n",
       "      <td>Not supported</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ed637828714417577685_889446803</td>\n",
       "      <td>2022-03-14 16:03:11.918913300+00:00</td>\n",
       "      <td>a085f9e3a00689554d351bdcdd4d503a1983eecd</td>\n",
       "      <td>desktop-6qf1uf2</td>\n",
       "      <td>High</td>\n",
       "      <td>UnwantedSoftware</td>\n",
       "      <td>Notepad Execution Detection</td>\n",
       "      <td>notepad.exe</td>\n",
       "      <td>5b80bbb07b1a84384e61fb3f9366cad97904ebea</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>25654</td>\n",
       "      <td>DeviceProcessEvents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>da637831027930681075_399960450</td>\n",
       "      <td>2022-03-17 08:32:13.537246500+00:00</td>\n",
       "      <td>a085f9e3a00689554d351bdcdd4d503a1983eecd</td>\n",
       "      <td>desktop-6qf1uf2</td>\n",
       "      <td>Low</td>\n",
       "      <td>Malware</td>\n",
       "      <td>'ATPMiniDump' hacktool was prevented</td>\n",
       "      <td>ATPMiniDump.exe</td>\n",
       "      <td>2659afd376e84ea8dab21d73412069677212abd6</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50216</td>\n",
       "      <td>DeviceEvents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>da637831027930681075_399960450</td>\n",
       "      <td>2022-03-17 08:32:41.619019500+00:00</td>\n",
       "      <td>a085f9e3a00689554d351bdcdd4d503a1983eecd</td>\n",
       "      <td>desktop-6qf1uf2</td>\n",
       "      <td>Low</td>\n",
       "      <td>Malware</td>\n",
       "      <td>'ATPMiniDump' hacktool was prevented</td>\n",
       "      <td>ATPMiniDump.exe</td>\n",
       "      <td>2659afd376e84ea8dab21d73412069677212abd6</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50232</td>\n",
       "      <td>DeviceEvents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>da637831027930681075_399960450</td>\n",
       "      <td>2022-03-17 08:32:51.787218300+00:00</td>\n",
       "      <td>a085f9e3a00689554d351bdcdd4d503a1983eecd</td>\n",
       "      <td>desktop-6qf1uf2</td>\n",
       "      <td>Low</td>\n",
       "      <td>Malware</td>\n",
       "      <td>'ATPMiniDump' hacktool was detected</td>\n",
       "      <td>ATPMiniDump.exe</td>\n",
       "      <td>2659afd376e84ea8dab21d73412069677212abd6</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50322</td>\n",
       "      <td>DeviceEvents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>da637831027930681075_399960450</td>\n",
       "      <td>2022-03-17 08:32:51.795963100+00:00</td>\n",
       "      <td>a085f9e3a00689554d351bdcdd4d503a1983eecd</td>\n",
       "      <td>desktop-6qf1uf2</td>\n",
       "      <td>Low</td>\n",
       "      <td>Malware</td>\n",
       "      <td>'ATPMiniDump' hacktool was detected</td>\n",
       "      <td>ATPMiniDump.exe</td>\n",
       "      <td>2659afd376e84ea8dab21d73412069677212abd6</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50351</td>\n",
       "      <td>DeviceEvents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>da637831029556671978_1988175326</td>\n",
       "      <td>2022-03-17 08:33:14.289264600+00:00</td>\n",
       "      <td>a085f9e3a00689554d351bdcdd4d503a1983eecd</td>\n",
       "      <td>desktop-6qf1uf2</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Malware</td>\n",
       "      <td>An active 'ATPMiniDump' hacktool process was d...</td>\n",
       "      <td>ATPMiniDump.exe</td>\n",
       "      <td>2659afd376e84ea8dab21d73412069677212abd6</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50540</td>\n",
       "      <td>DeviceEvents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>da637831027930681075_399960450</td>\n",
       "      <td>2022-03-17 08:33:14.289301300+00:00</td>\n",
       "      <td>a085f9e3a00689554d351bdcdd4d503a1983eecd</td>\n",
       "      <td>desktop-6qf1uf2</td>\n",
       "      <td>Low</td>\n",
       "      <td>Malware</td>\n",
       "      <td>'ATPMiniDump' hacktool was detected</td>\n",
       "      <td>ATPMiniDump.exe</td>\n",
       "      <td>2659afd376e84ea8dab21d73412069677212abd6</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50541</td>\n",
       "      <td>DeviceEvents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>da637831027930681075_399960450</td>\n",
       "      <td>2022-03-17 08:33:14.505995400+00:00</td>\n",
       "      <td>a085f9e3a00689554d351bdcdd4d503a1983eecd</td>\n",
       "      <td>desktop-6qf1uf2</td>\n",
       "      <td>Low</td>\n",
       "      <td>Malware</td>\n",
       "      <td>'ATPMiniDump' hacktool was detected</td>\n",
       "      <td>ATPMiniDump.exe</td>\n",
       "      <td>2659afd376e84ea8dab21d73412069677212abd6</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50521</td>\n",
       "      <td>DeviceEvents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>da637831931160791947_311302490</td>\n",
       "      <td>2022-03-18 09:36:44.691315900+00:00</td>\n",
       "      <td>e09b459fcd3aa36f23084211c34bfca046848b65</td>\n",
       "      <td>atevet06cl005.defenderatevet06.onmicrosoft.com</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Execution</td>\n",
       "      <td>Suspicious PowerShell command line</td>\n",
       "      <td>powershell.exe</td>\n",
       "      <td>f43d9bb316e30ae1a3494ac5b0624f6bea1bf054</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[\"PowerShell (T1059.001)\"]</td>\n",
       "      <td>127261</td>\n",
       "      <td>DeviceImageLoadEvents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>da637831931152888965_11461089</td>\n",
       "      <td>2022-03-18 09:36:44.691315900+00:00</td>\n",
       "      <td>e09b459fcd3aa36f23084211c34bfca046848b65</td>\n",
       "      <td>atevet06cl005.defenderatevet06.onmicrosoft.com</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Execution</td>\n",
       "      <td>Suspicious PowerShell command line</td>\n",
       "      <td>powershell.exe</td>\n",
       "      <td>f43d9bb316e30ae1a3494ac5b0624f6bea1bf054</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[\"PowerShell (T1059.001)\"]</td>\n",
       "      <td>127261</td>\n",
       "      <td>DeviceImageLoadEvents</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            AlertId                           Timestamp  \\\n",
       "0    ed637828714417577685_889446803 2022-03-14 16:03:11.918913300+00:00   \n",
       "1    ed637828714417577685_889446803 2022-03-14 16:03:11.918913300+00:00   \n",
       "2    da637831027930681075_399960450 2022-03-17 08:32:13.537246500+00:00   \n",
       "3    da637831027930681075_399960450 2022-03-17 08:32:41.619019500+00:00   \n",
       "4    da637831027930681075_399960450 2022-03-17 08:32:51.787218300+00:00   \n",
       "5    da637831027930681075_399960450 2022-03-17 08:32:51.795963100+00:00   \n",
       "6   da637831029556671978_1988175326 2022-03-17 08:33:14.289264600+00:00   \n",
       "7    da637831027930681075_399960450 2022-03-17 08:33:14.289301300+00:00   \n",
       "8    da637831027930681075_399960450 2022-03-17 08:33:14.505995400+00:00   \n",
       "9    da637831931160791947_311302490 2022-03-18 09:36:44.691315900+00:00   \n",
       "10    da637831931152888965_11461089 2022-03-18 09:36:44.691315900+00:00   \n",
       "\n",
       "                                    DeviceId  \\\n",
       "0   a085f9e3a00689554d351bdcdd4d503a1983eecd   \n",
       "1   a085f9e3a00689554d351bdcdd4d503a1983eecd   \n",
       "2   a085f9e3a00689554d351bdcdd4d503a1983eecd   \n",
       "3   a085f9e3a00689554d351bdcdd4d503a1983eecd   \n",
       "4   a085f9e3a00689554d351bdcdd4d503a1983eecd   \n",
       "5   a085f9e3a00689554d351bdcdd4d503a1983eecd   \n",
       "6   a085f9e3a00689554d351bdcdd4d503a1983eecd   \n",
       "7   a085f9e3a00689554d351bdcdd4d503a1983eecd   \n",
       "8   a085f9e3a00689554d351bdcdd4d503a1983eecd   \n",
       "9   e09b459fcd3aa36f23084211c34bfca046848b65   \n",
       "10  e09b459fcd3aa36f23084211c34bfca046848b65   \n",
       "\n",
       "                                        DeviceName Severity          Category  \\\n",
       "0                                  desktop-6qf1uf2     High  UnwantedSoftware   \n",
       "1                                  desktop-6qf1uf2     High  UnwantedSoftware   \n",
       "2                                  desktop-6qf1uf2      Low           Malware   \n",
       "3                                  desktop-6qf1uf2      Low           Malware   \n",
       "4                                  desktop-6qf1uf2      Low           Malware   \n",
       "5                                  desktop-6qf1uf2      Low           Malware   \n",
       "6                                  desktop-6qf1uf2   Medium           Malware   \n",
       "7                                  desktop-6qf1uf2      Low           Malware   \n",
       "8                                  desktop-6qf1uf2      Low           Malware   \n",
       "9   atevet06cl005.defenderatevet06.onmicrosoft.com   Medium         Execution   \n",
       "10  atevet06cl005.defenderatevet06.onmicrosoft.com   Medium         Execution   \n",
       "\n",
       "                                                Title         FileName  \\\n",
       "0                         Notepad Execution Detection      notepad.exe   \n",
       "1                         Notepad Execution Detection      notepad.exe   \n",
       "2                'ATPMiniDump' hacktool was prevented  ATPMiniDump.exe   \n",
       "3                'ATPMiniDump' hacktool was prevented  ATPMiniDump.exe   \n",
       "4                 'ATPMiniDump' hacktool was detected  ATPMiniDump.exe   \n",
       "5                 'ATPMiniDump' hacktool was detected  ATPMiniDump.exe   \n",
       "6   An active 'ATPMiniDump' hacktool process was d...  ATPMiniDump.exe   \n",
       "7                 'ATPMiniDump' hacktool was detected  ATPMiniDump.exe   \n",
       "8                 'ATPMiniDump' hacktool was detected  ATPMiniDump.exe   \n",
       "9                  Suspicious PowerShell command line   powershell.exe   \n",
       "10                 Suspicious PowerShell command line   powershell.exe   \n",
       "\n",
       "                                        SHA1 RemoteUrl RemoteIP  \\\n",
       "0   5b80bbb07b1a84384e61fb3f9366cad97904ebea                      \n",
       "1   5b80bbb07b1a84384e61fb3f9366cad97904ebea                      \n",
       "2   2659afd376e84ea8dab21d73412069677212abd6                      \n",
       "3   2659afd376e84ea8dab21d73412069677212abd6                      \n",
       "4   2659afd376e84ea8dab21d73412069677212abd6                      \n",
       "5   2659afd376e84ea8dab21d73412069677212abd6                      \n",
       "6   2659afd376e84ea8dab21d73412069677212abd6                      \n",
       "7   2659afd376e84ea8dab21d73412069677212abd6                      \n",
       "8   2659afd376e84ea8dab21d73412069677212abd6                      \n",
       "9   f43d9bb316e30ae1a3494ac5b0624f6bea1bf054                      \n",
       "10  f43d9bb316e30ae1a3494ac5b0624f6bea1bf054                      \n",
       "\n",
       "              AttackTechniques  ReportId                  Table  \n",
       "0                                  25654          Not supported  \n",
       "1                                  25654    DeviceProcessEvents  \n",
       "2                                  50216           DeviceEvents  \n",
       "3                                  50232           DeviceEvents  \n",
       "4                                  50322           DeviceEvents  \n",
       "5                                  50351           DeviceEvents  \n",
       "6                                  50540           DeviceEvents  \n",
       "7                                  50541           DeviceEvents  \n",
       "8                                  50521           DeviceEvents  \n",
       "9   [\"PowerShell (T1059.001)\"]    127261  DeviceImageLoadEvents  \n",
       "10  [\"PowerShell (T1059.001)\"]    127261  DeviceImageLoadEvents  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "md_prov.MDATP.list_alerts(start=-10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Execute queries with custom parameters:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Severity</th>\n",
       "      <th>count_</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>High</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Medium</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Low</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Informational</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Severity  count_\n",
       "0           High      53\n",
       "1         Medium      11\n",
       "2            Low      19\n",
       "3  Informational       6"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "md_prov.MDATP.list_alerts(start=\"-30\", add_query_items=\"| summarize count() by Severity\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Print a fully constructed query for debug purposes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "' DeviceAlertEvents | where Timestamp >= datetime(2022-02-19T18:31:48.373684Z) | where Timestamp <= datetime(2022-03-22T18:31:44.860951Z) | summarize count() by Severity'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "md_prov.MDATP.list_alerts(\"print\", start=\"-30\", add_query_items=\"| summarize count() by Severity\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Execute a custom query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AlertId</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>DeviceId</th>\n",
       "      <th>DeviceName</th>\n",
       "      <th>Severity</th>\n",
       "      <th>Category</th>\n",
       "      <th>Title</th>\n",
       "      <th>FileName</th>\n",
       "      <th>SHA1</th>\n",
       "      <th>RemoteUrl</th>\n",
       "      <th>RemoteIP</th>\n",
       "      <th>AttackTechniques</th>\n",
       "      <th>ReportId</th>\n",
       "      <th>Table</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>da637807989907659907_-236366407</td>\n",
       "      <td>2022-02-21 09:47:47.938238100+00:00</td>\n",
       "      <td>efeb32222798ea6c2eb747514dd34e646cf751aa</td>\n",
       "      <td>win-6l54m49tpgn.woanware.co.uk</td>\n",
       "      <td>Low</td>\n",
       "      <td>Discovery</td>\n",
       "      <td>Suspicious System Service Discovery</td>\n",
       "      <td>powershell.exe</td>\n",
       "      <td>5b1a36a8b206fe9daece7e2cd702cdd4e10bb895</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[\"System Service Discovery (T1007)\",\"Windows M...</td>\n",
       "      <td>45923</td>\n",
       "      <td>DeviceEvents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>da637824441902905094_1268174690</td>\n",
       "      <td>2022-03-09 17:34:15.638562100+00:00</td>\n",
       "      <td>8a2d7be2ee62093058401b4d50c481fe9ec7aedb</td>\n",
       "      <td>attackervm.attack.vm</td>\n",
       "      <td>High</td>\n",
       "      <td>Persistence</td>\n",
       "      <td>A suspicious DLL was loaded by the ADFS service</td>\n",
       "      <td>version.dll</td>\n",
       "      <td>ebdcd4c1743adc9714a94a09eeb770c8b67747cc</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[\"T1606\"]</td>\n",
       "      <td>718</td>\n",
       "      <td>DeviceImageLoadEvents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>da637807989904403259_2043617699</td>\n",
       "      <td>2022-02-21 09:50:28.356919+00:00</td>\n",
       "      <td>efeb32222798ea6c2eb747514dd34e646cf751aa</td>\n",
       "      <td>win-6l54m49tpgn.woanware.co.uk</td>\n",
       "      <td>Low</td>\n",
       "      <td>Discovery</td>\n",
       "      <td>Suspicious User Account Discovery</td>\n",
       "      <td>explorer.exe</td>\n",
       "      <td>dbf66924d79d246da08df40db1f20595385b6347</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[\"Remote System Discovery (T1018)\",\"PowerShell...</td>\n",
       "      <td>46011</td>\n",
       "      <td>DeviceRegistryEvents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ed637810396041195799_-785916563</td>\n",
       "      <td>2022-02-23 12:42:05.423489800+00:00</td>\n",
       "      <td>efeb32222798ea6c2eb747514dd34e646cf751aa</td>\n",
       "      <td>win-6l54m49tpgn.woanware.co.uk</td>\n",
       "      <td>High</td>\n",
       "      <td>UnwantedSoftware</td>\n",
       "      <td>Notepad Execution Detection</td>\n",
       "      <td>notepad.exe</td>\n",
       "      <td>58bf532f93949cc62821273f778b87da85787afa</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>7173</td>\n",
       "      <td>DeviceProcessEvents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ed637810396041195799_-785916563</td>\n",
       "      <td>2022-02-21 12:50:38.523957+00:00</td>\n",
       "      <td>efeb32222798ea6c2eb747514dd34e646cf751aa</td>\n",
       "      <td>win-6l54m49tpgn.woanware.co.uk</td>\n",
       "      <td>High</td>\n",
       "      <td>UnwantedSoftware</td>\n",
       "      <td>Notepad Execution Detection</td>\n",
       "      <td>notepad.exe</td>\n",
       "      <td>58bf532f93949cc62821273f778b87da85787afa</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>48272</td>\n",
       "      <td>DeviceProcessEvents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>da637831931152888965_11461089</td>\n",
       "      <td>2022-03-18 09:36:44.691315900+00:00</td>\n",
       "      <td>e09b459fcd3aa36f23084211c34bfca046848b65</td>\n",
       "      <td>atevet06cl005.defenderatevet06.onmicrosoft.com</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Execution</td>\n",
       "      <td>Suspicious PowerShell command line</td>\n",
       "      <td>powershell.exe</td>\n",
       "      <td>f43d9bb316e30ae1a3494ac5b0624f6bea1bf054</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[\"PowerShell (T1059.001)\"]</td>\n",
       "      <td>127261</td>\n",
       "      <td>DeviceImageLoadEvents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>da637823935780152927_1691659474</td>\n",
       "      <td>2022-03-09 03:31:17.271601600+00:00</td>\n",
       "      <td>e09b459fcd3aa36f23084211c34bfca046848b65</td>\n",
       "      <td>atevet06cl005.defenderatevet06.onmicrosoft.com</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Persistence</td>\n",
       "      <td>A WMI event filter was bound to a suspicious e...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[\"Windows Management Instrumentation Event Sub...</td>\n",
       "      <td>771</td>\n",
       "      <td>DeviceEvents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>da637819900757032477_-2012458047</td>\n",
       "      <td>2022-03-04 11:20:44.426056800+00:00</td>\n",
       "      <td>efeb32222798ea6c2eb747514dd34e646cf751aa</td>\n",
       "      <td>win-6l54m49tpgn.woanware.co.uk</td>\n",
       "      <td>Low</td>\n",
       "      <td>DefenseEvasion</td>\n",
       "      <td>Windows Sysinternals tool renamed</td>\n",
       "      <td>powershell.exe</td>\n",
       "      <td>5b1a36a8b206fe9daece7e2cd702cdd4e10bb895</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[\"Masquerading (T1036)\"]</td>\n",
       "      <td>43063</td>\n",
       "      <td>DeviceProcessEvents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>da637823935780204986_825228657</td>\n",
       "      <td>2022-03-09 03:31:17.272119+00:00</td>\n",
       "      <td>e09b459fcd3aa36f23084211c34bfca046848b65</td>\n",
       "      <td>atevet06cl005.defenderatevet06.onmicrosoft.com</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Persistence</td>\n",
       "      <td>A WMI event filter was bound to a suspicious e...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[\"Windows Management Instrumentation Event Sub...</td>\n",
       "      <td>774</td>\n",
       "      <td>DeviceEvents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ed637828714417577685_889446803</td>\n",
       "      <td>2022-03-14 16:03:11.918913300+00:00</td>\n",
       "      <td>a085f9e3a00689554d351bdcdd4d503a1983eecd</td>\n",
       "      <td>desktop-6qf1uf2</td>\n",
       "      <td>High</td>\n",
       "      <td>UnwantedSoftware</td>\n",
       "      <td>Notepad Execution Detection</td>\n",
       "      <td>notepad.exe</td>\n",
       "      <td>5b80bbb07b1a84384e61fb3f9366cad97904ebea</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>25654</td>\n",
       "      <td>Not supported</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            AlertId                           Timestamp  \\\n",
       "0   da637807989907659907_-236366407 2022-02-21 09:47:47.938238100+00:00   \n",
       "1   da637824441902905094_1268174690 2022-03-09 17:34:15.638562100+00:00   \n",
       "2   da637807989904403259_2043617699    2022-02-21 09:50:28.356919+00:00   \n",
       "3   ed637810396041195799_-785916563 2022-02-23 12:42:05.423489800+00:00   \n",
       "4   ed637810396041195799_-785916563    2022-02-21 12:50:38.523957+00:00   \n",
       "5     da637831931152888965_11461089 2022-03-18 09:36:44.691315900+00:00   \n",
       "6   da637823935780152927_1691659474 2022-03-09 03:31:17.271601600+00:00   \n",
       "7  da637819900757032477_-2012458047 2022-03-04 11:20:44.426056800+00:00   \n",
       "8    da637823935780204986_825228657    2022-03-09 03:31:17.272119+00:00   \n",
       "9    ed637828714417577685_889446803 2022-03-14 16:03:11.918913300+00:00   \n",
       "\n",
       "                                   DeviceId  \\\n",
       "0  efeb32222798ea6c2eb747514dd34e646cf751aa   \n",
       "1  8a2d7be2ee62093058401b4d50c481fe9ec7aedb   \n",
       "2  efeb32222798ea6c2eb747514dd34e646cf751aa   \n",
       "3  efeb32222798ea6c2eb747514dd34e646cf751aa   \n",
       "4  efeb32222798ea6c2eb747514dd34e646cf751aa   \n",
       "5  e09b459fcd3aa36f23084211c34bfca046848b65   \n",
       "6  e09b459fcd3aa36f23084211c34bfca046848b65   \n",
       "7  efeb32222798ea6c2eb747514dd34e646cf751aa   \n",
       "8  e09b459fcd3aa36f23084211c34bfca046848b65   \n",
       "9  a085f9e3a00689554d351bdcdd4d503a1983eecd   \n",
       "\n",
       "                                       DeviceName Severity          Category  \\\n",
       "0                  win-6l54m49tpgn.woanware.co.uk      Low         Discovery   \n",
       "1                            attackervm.attack.vm     High       Persistence   \n",
       "2                  win-6l54m49tpgn.woanware.co.uk      Low         Discovery   \n",
       "3                  win-6l54m49tpgn.woanware.co.uk     High  UnwantedSoftware   \n",
       "4                  win-6l54m49tpgn.woanware.co.uk     High  UnwantedSoftware   \n",
       "5  atevet06cl005.defenderatevet06.onmicrosoft.com   Medium         Execution   \n",
       "6  atevet06cl005.defenderatevet06.onmicrosoft.com   Medium       Persistence   \n",
       "7                  win-6l54m49tpgn.woanware.co.uk      Low    DefenseEvasion   \n",
       "8  atevet06cl005.defenderatevet06.onmicrosoft.com   Medium       Persistence   \n",
       "9                                 desktop-6qf1uf2     High  UnwantedSoftware   \n",
       "\n",
       "                                               Title        FileName  \\\n",
       "0                Suspicious System Service Discovery  powershell.exe   \n",
       "1    A suspicious DLL was loaded by the ADFS service     version.dll   \n",
       "2                  Suspicious User Account Discovery    explorer.exe   \n",
       "3                        Notepad Execution Detection     notepad.exe   \n",
       "4                        Notepad Execution Detection     notepad.exe   \n",
       "5                 Suspicious PowerShell command line  powershell.exe   \n",
       "6  A WMI event filter was bound to a suspicious e...                   \n",
       "7                  Windows Sysinternals tool renamed  powershell.exe   \n",
       "8  A WMI event filter was bound to a suspicious e...                   \n",
       "9                        Notepad Execution Detection     notepad.exe   \n",
       "\n",
       "                                       SHA1 RemoteUrl RemoteIP  \\\n",
       "0  5b1a36a8b206fe9daece7e2cd702cdd4e10bb895                      \n",
       "1  ebdcd4c1743adc9714a94a09eeb770c8b67747cc                      \n",
       "2  dbf66924d79d246da08df40db1f20595385b6347                      \n",
       "3  58bf532f93949cc62821273f778b87da85787afa                      \n",
       "4  58bf532f93949cc62821273f778b87da85787afa                      \n",
       "5  f43d9bb316e30ae1a3494ac5b0624f6bea1bf054                      \n",
       "6                                                                \n",
       "7  5b1a36a8b206fe9daece7e2cd702cdd4e10bb895                      \n",
       "8                                                                \n",
       "9  5b80bbb07b1a84384e61fb3f9366cad97904ebea                      \n",
       "\n",
       "                                    AttackTechniques  ReportId  \\\n",
       "0  [\"System Service Discovery (T1007)\",\"Windows M...     45923   \n",
       "1                                          [\"T1606\"]       718   \n",
       "2  [\"Remote System Discovery (T1018)\",\"PowerShell...     46011   \n",
       "3                                                         7173   \n",
       "4                                                        48272   \n",
       "5                         [\"PowerShell (T1059.001)\"]    127261   \n",
       "6  [\"Windows Management Instrumentation Event Sub...       771   \n",
       "7                           [\"Masquerading (T1036)\"]     43063   \n",
       "8  [\"Windows Management Instrumentation Event Sub...       774   \n",
       "9                                                        25654   \n",
       "\n",
       "                   Table  \n",
       "0           DeviceEvents  \n",
       "1  DeviceImageLoadEvents  \n",
       "2   DeviceRegistryEvents  \n",
       "3    DeviceProcessEvents  \n",
       "4    DeviceProcessEvents  \n",
       "5  DeviceImageLoadEvents  \n",
       "6           DeviceEvents  \n",
       "7    DeviceProcessEvents  \n",
       "8           DeviceEvents  \n",
       "9          Not supported  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"DeviceAlertEvents | sample 10\"\n",
    "md_prov.exec_query(query)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
